<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>聚合函数 | 知识库</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/noteslibrary/img/favicon.ico">
    <meta name="description" content="Java后端技术博客,专注Java后端学习与总结。HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github等技术文章。">
    <meta name="keywords" content="后端博客,个人技术博客,后端,后端开发,后端框架,后端面试题,技术文档,学习,面试,HTML,CSS,JavaScript,TypeScript,JavaSE,Python,Vue,React,Node,Git,Github">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/noteslibrary/assets/css/0.styles.2500ff6d.css" as="style"><link rel="preload" href="/noteslibrary/assets/js/app.dc0c3d24.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/2.a664539c.js" as="script"><link rel="preload" href="/noteslibrary/assets/js/95.f7232d68.js" as="script"><link rel="prefetch" href="/noteslibrary/assets/js/10.bab1a8b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/100.245d2d1d.js"><link rel="prefetch" href="/noteslibrary/assets/js/101.9d1c6c13.js"><link rel="prefetch" href="/noteslibrary/assets/js/102.d187686f.js"><link rel="prefetch" href="/noteslibrary/assets/js/103.b0e7acf6.js"><link rel="prefetch" href="/noteslibrary/assets/js/104.895c9e53.js"><link rel="prefetch" href="/noteslibrary/assets/js/105.235036be.js"><link rel="prefetch" href="/noteslibrary/assets/js/106.17a71ae1.js"><link rel="prefetch" href="/noteslibrary/assets/js/107.256ceb5d.js"><link rel="prefetch" href="/noteslibrary/assets/js/108.57832d85.js"><link rel="prefetch" href="/noteslibrary/assets/js/109.f8b1d1c7.js"><link rel="prefetch" href="/noteslibrary/assets/js/11.2c69801a.js"><link rel="prefetch" href="/noteslibrary/assets/js/110.f3ce6a68.js"><link rel="prefetch" href="/noteslibrary/assets/js/111.bdfd28f4.js"><link rel="prefetch" href="/noteslibrary/assets/js/112.878f599c.js"><link rel="prefetch" href="/noteslibrary/assets/js/113.0efa07cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/114.c7aba251.js"><link rel="prefetch" href="/noteslibrary/assets/js/115.8b2e628d.js"><link rel="prefetch" href="/noteslibrary/assets/js/116.3392d8ba.js"><link rel="prefetch" href="/noteslibrary/assets/js/117.310538d0.js"><link rel="prefetch" href="/noteslibrary/assets/js/118.81eb015b.js"><link rel="prefetch" href="/noteslibrary/assets/js/12.e77f5a18.js"><link rel="prefetch" href="/noteslibrary/assets/js/13.d24d0054.js"><link rel="prefetch" href="/noteslibrary/assets/js/14.f29d4d33.js"><link rel="prefetch" href="/noteslibrary/assets/js/15.8694e081.js"><link rel="prefetch" href="/noteslibrary/assets/js/16.03ffb143.js"><link rel="prefetch" href="/noteslibrary/assets/js/17.83edd7b9.js"><link rel="prefetch" href="/noteslibrary/assets/js/18.58ca4447.js"><link rel="prefetch" href="/noteslibrary/assets/js/19.e136d5c2.js"><link rel="prefetch" href="/noteslibrary/assets/js/20.fd3961b6.js"><link rel="prefetch" href="/noteslibrary/assets/js/21.207406c6.js"><link rel="prefetch" href="/noteslibrary/assets/js/22.b8738ce2.js"><link rel="prefetch" href="/noteslibrary/assets/js/23.33e4529d.js"><link rel="prefetch" href="/noteslibrary/assets/js/24.ab5493c5.js"><link rel="prefetch" href="/noteslibrary/assets/js/25.2506ce48.js"><link rel="prefetch" href="/noteslibrary/assets/js/26.7e6a9c14.js"><link rel="prefetch" href="/noteslibrary/assets/js/27.e7b4e92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/28.7ad46ba6.js"><link rel="prefetch" href="/noteslibrary/assets/js/29.81666f41.js"><link rel="prefetch" href="/noteslibrary/assets/js/3.8f13cd17.js"><link rel="prefetch" href="/noteslibrary/assets/js/30.07ada09b.js"><link rel="prefetch" href="/noteslibrary/assets/js/31.f271c8cf.js"><link rel="prefetch" href="/noteslibrary/assets/js/32.2350914c.js"><link rel="prefetch" href="/noteslibrary/assets/js/33.9d7bfb77.js"><link rel="prefetch" href="/noteslibrary/assets/js/34.6fcf6f6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/35.0c3a88fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/36.574ca92a.js"><link rel="prefetch" href="/noteslibrary/assets/js/37.72e38074.js"><link rel="prefetch" href="/noteslibrary/assets/js/38.16d408fb.js"><link rel="prefetch" href="/noteslibrary/assets/js/39.63abc4a8.js"><link rel="prefetch" href="/noteslibrary/assets/js/4.3e68fc94.js"><link rel="prefetch" href="/noteslibrary/assets/js/40.ca2bdd48.js"><link rel="prefetch" href="/noteslibrary/assets/js/41.f82c80ec.js"><link rel="prefetch" href="/noteslibrary/assets/js/42.d1b8f579.js"><link rel="prefetch" href="/noteslibrary/assets/js/43.d3fd6260.js"><link rel="prefetch" href="/noteslibrary/assets/js/44.491f9afa.js"><link rel="prefetch" href="/noteslibrary/assets/js/45.1ee0d084.js"><link rel="prefetch" href="/noteslibrary/assets/js/46.a3e0be1f.js"><link rel="prefetch" href="/noteslibrary/assets/js/47.647908d1.js"><link rel="prefetch" href="/noteslibrary/assets/js/48.9658b8b0.js"><link rel="prefetch" href="/noteslibrary/assets/js/49.8f4b8327.js"><link rel="prefetch" href="/noteslibrary/assets/js/5.a0a97ccd.js"><link rel="prefetch" href="/noteslibrary/assets/js/50.f46d1433.js"><link rel="prefetch" href="/noteslibrary/assets/js/51.f1b784a0.js"><link rel="prefetch" href="/noteslibrary/assets/js/52.cce45956.js"><link rel="prefetch" href="/noteslibrary/assets/js/53.d66dabe3.js"><link rel="prefetch" href="/noteslibrary/assets/js/54.384b864b.js"><link rel="prefetch" href="/noteslibrary/assets/js/55.47ed19f2.js"><link rel="prefetch" href="/noteslibrary/assets/js/56.193cd456.js"><link rel="prefetch" href="/noteslibrary/assets/js/57.e6ea1f8c.js"><link rel="prefetch" href="/noteslibrary/assets/js/58.97fd2330.js"><link rel="prefetch" href="/noteslibrary/assets/js/59.b0c3d9ea.js"><link rel="prefetch" href="/noteslibrary/assets/js/6.50cbd75f.js"><link rel="prefetch" href="/noteslibrary/assets/js/60.d01d0651.js"><link rel="prefetch" href="/noteslibrary/assets/js/61.385e9bae.js"><link rel="prefetch" href="/noteslibrary/assets/js/62.a93fa4c8.js"><link rel="prefetch" href="/noteslibrary/assets/js/63.f72a2142.js"><link rel="prefetch" href="/noteslibrary/assets/js/64.3bf0b024.js"><link rel="prefetch" href="/noteslibrary/assets/js/65.cb1cb3bb.js"><link rel="prefetch" href="/noteslibrary/assets/js/66.4c9ff8cd.js"><link rel="prefetch" href="/noteslibrary/assets/js/67.2fc17900.js"><link rel="prefetch" href="/noteslibrary/assets/js/68.fd3ee410.js"><link rel="prefetch" href="/noteslibrary/assets/js/69.682be05d.js"><link rel="prefetch" href="/noteslibrary/assets/js/7.80203dee.js"><link rel="prefetch" href="/noteslibrary/assets/js/70.29428a45.js"><link rel="prefetch" href="/noteslibrary/assets/js/71.aff6ef6b.js"><link rel="prefetch" href="/noteslibrary/assets/js/72.fe7572e0.js"><link rel="prefetch" href="/noteslibrary/assets/js/73.e52bc1a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/74.5e72ee84.js"><link rel="prefetch" href="/noteslibrary/assets/js/75.5a5bba64.js"><link rel="prefetch" href="/noteslibrary/assets/js/76.70d6ff7c.js"><link rel="prefetch" href="/noteslibrary/assets/js/77.1db1c302.js"><link rel="prefetch" href="/noteslibrary/assets/js/78.137c92e8.js"><link rel="prefetch" href="/noteslibrary/assets/js/79.8455d34b.js"><link rel="prefetch" href="/noteslibrary/assets/js/8.9e13e493.js"><link rel="prefetch" href="/noteslibrary/assets/js/80.127f3a4e.js"><link rel="prefetch" href="/noteslibrary/assets/js/81.191d3614.js"><link rel="prefetch" href="/noteslibrary/assets/js/82.6ae31745.js"><link rel="prefetch" href="/noteslibrary/assets/js/83.640cd3d7.js"><link rel="prefetch" href="/noteslibrary/assets/js/84.22c036e2.js"><link rel="prefetch" href="/noteslibrary/assets/js/85.692cd496.js"><link rel="prefetch" href="/noteslibrary/assets/js/86.793e38a7.js"><link rel="prefetch" href="/noteslibrary/assets/js/87.eb27f1d6.js"><link rel="prefetch" href="/noteslibrary/assets/js/88.6d48c75e.js"><link rel="prefetch" href="/noteslibrary/assets/js/89.a798bacb.js"><link rel="prefetch" href="/noteslibrary/assets/js/9.7e864ff7.js"><link rel="prefetch" href="/noteslibrary/assets/js/90.e814aadc.js"><link rel="prefetch" href="/noteslibrary/assets/js/91.5c132772.js"><link rel="prefetch" href="/noteslibrary/assets/js/92.d0d1984d.js"><link rel="prefetch" href="/noteslibrary/assets/js/93.bbc81ca6.js"><link rel="prefetch" href="/noteslibrary/assets/js/94.242fbc29.js"><link rel="prefetch" href="/noteslibrary/assets/js/96.440e6bbe.js"><link rel="prefetch" href="/noteslibrary/assets/js/97.a8ad3aa8.js"><link rel="prefetch" href="/noteslibrary/assets/js/98.e9a01f98.js"><link rel="prefetch" href="/noteslibrary/assets/js/99.e024f354.js">
    <link rel="stylesheet" href="/noteslibrary/assets/css/0.styles.2500ff6d.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/noteslibrary/" class="home-link router-link-active"><img src="/noteslibrary/img/EB-logo.png" alt="知识库" class="logo"> <span class="site-name can-hide">知识库</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/2dfe6d/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/2dfe6d/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/2dfe6d/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><!----> <nav class="nav-links"><div class="nav-item"><a href="/noteslibrary/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="基础" class="dropdown-title"><a href="/noteslibrary/basis/" class="link-title">基础</a> <span class="title" style="display:none;">基础</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机网络》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《计算机组成原理》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《操作系统》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《数据结构》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《算法》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/9a7ee40fc232253e/" class="nav-link">《设计模式》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/noteslibrary/fontend/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/0796ba76b4b55368.html" class="nav-link">《JS笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/f344d070a1031ef7.html" class="nav-link">《ES6笔记》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/note/vue/" class="nav-link">《Vue笔记》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/noteslibrary/backend/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>JavaEE框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/d0b17d/" class="nav-link">《DAO全家桶》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/a9ac80/" class="nav-link">《Spring全家桶》</a></li></ul></li><li class="dropdown-item"><h4>BigData框架</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/2dfe6d/404" class="nav-link">《Hadoop生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/2dfe6d/404" class="nav-link">《Spark生态》</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/2dfe6d/404" class="nav-link">《Flink生态》</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><a href="/noteslibrary/database/" class="link-title">数据库</a> <span class="title" style="display:none;">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/32e9fd/" class="nav-link">MySQL</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="工具" class="dropdown-title"><a href="/noteslibrary/tools/" class="link-title">工具</a> <span class="title" style="display:none;">工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac065/" class="nav-link">Maven</a></li><li class="dropdown-subitem"><a href="/noteslibrary/pages/5760de/" class="nav-link">Git</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="收藏" class="dropdown-title"><a href="/noteslibrary/collect/" class="link-title">收藏</a> <span class="title" style="display:none;">收藏</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4></h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/50c072/" class="nav-link">博客收藏</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="其他" class="dropdown-title"><a href="/noteslibrary/others/" class="link-title">其他</a> <span class="title" style="display:none;">其他</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>博客部署</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/db737d/" class="nav-link">Hexo</a></li></ul></li><li class="dropdown-item"><h4>系统安装</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/3ac066/" class="nav-link">系统安装</a></li></ul></li><li class="dropdown-item"><h4>idea快捷键</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/noteslibrary/pages/111c73/" class="nav-link">idea快捷键</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/noteslibrary/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/noteslibrary/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/noteslibrary/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/lcfqzd/vuepress-theme-vdoing" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/noteslibrary/pages/32e9fd/" class="sidebar-link">写在前面</a></li><li><a href="/noteslibrary/pages/cfda94/" class="sidebar-link">数据库概述</a></li><li><a href="/noteslibrary/pages/497bcb/" class="sidebar-link">MySQL环境搭建</a></li><li><a href="/noteslibrary/pages/9c9f3a/" class="sidebar-link">基本的SELECT语句</a></li><li><a href="/noteslibrary/pages/b59830/" class="sidebar-link">运算符</a></li><li><a href="/noteslibrary/pages/6fef5b/" class="sidebar-link">排序与分页</a></li><li><a href="/noteslibrary/pages/3a50a8/" class="sidebar-link">多表查询</a></li><li><a href="/noteslibrary/pages/8de5b4/" class="sidebar-link">单行函数</a></li><li><a href="/noteslibrary/pages/2dfe6d/" aria-current="page" class="active sidebar-link">聚合函数</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/2dfe6d/#_1-聚合函数介绍" class="sidebar-link">1. 聚合函数介绍</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_1-1-avg和sum函数" class="sidebar-link">1.1 AVG和SUM函数</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_1-2-min和max函数" class="sidebar-link">1.2 MIN和MAX函数</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_1-3-count函数" class="sidebar-link">1.3 COUNT函数</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/2dfe6d/#_2-group-by" class="sidebar-link">2. GROUP BY</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_2-1-基本使用" class="sidebar-link">2.1 基本使用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_2-2-使用多个列分组" class="sidebar-link">2.2 使用多个列分组</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_2-3-group-by中使用with-rollup" class="sidebar-link">2.3 GROUP BY中使用WITH ROLLUP</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/2dfe6d/#_3-having" class="sidebar-link">3. HAVING</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_3-1-基本使用" class="sidebar-link">3.1 基本使用</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_3-2-where和having的对比" class="sidebar-link">3.2 WHERE和HAVING的对比</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/noteslibrary/pages/2dfe6d/#_4-select的执行过程" class="sidebar-link">4. SELECT的执行过程</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_4-1-查询的结构" class="sidebar-link">4.1 查询的结构</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_4-2-select执行顺序" class="sidebar-link">4.2 SELECT执行顺序</a></li><li class="sidebar-sub-header level3"><a href="/noteslibrary/pages/2dfe6d/#_4-3-sql-的执行原理" class="sidebar-link">4.3 SQL 的执行原理</a></li></ul></li></ul></li><li><a href="/noteslibrary/pages/3ea5ac/" class="sidebar-link">子查询</a></li><li><a href="/noteslibrary/pages/70a90b/" class="sidebar-link">创建和管理表</a></li><li><a href="/noteslibrary/pages/a20c5b/" class="sidebar-link">数据处理之增删改</a></li><li><a href="/noteslibrary/pages/9515c0/" class="sidebar-link">MySQL数据类型精讲</a></li><li><a href="/noteslibrary/pages/98e681/" class="sidebar-link">约束</a></li><li><a href="/noteslibrary/pages/4535e0/" class="sidebar-link">视图</a></li><li><a href="/noteslibrary/pages/e5e604/" class="sidebar-link">存储过程与函数</a></li><li><a href="/noteslibrary/pages/f68b46/" class="sidebar-link">变量与流程控制与游标</a></li><li><a href="/noteslibrary/pages/fe941e/" class="sidebar-link">触发器</a></li><li><a href="/noteslibrary/pages/efad7b/" class="sidebar-link">MySQL8其它新特性</a></li></ul></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/noteslibrary/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/noteslibrary/database/#数据库" data-v-06225672>数据库</a></li><li data-v-06225672><a href="/noteslibrary/database/#MySQL笔记" data-v-06225672>MySQL笔记</a></li></ul> <div class="info" data-v-06225672><!----> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-04-05</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">聚合函数<!----></h1> <!----> <div class="theme-vdoing-content content__default"><h1 id="聚合函数"><a href="#聚合函数" class="header-anchor">#</a> 聚合函数</h1> <h2 id="_1-聚合函数介绍"><a href="#_1-聚合函数介绍" class="header-anchor">#</a> 1. 聚合函数介绍</h2> <ul><li>什么是聚合函数
聚合函数作用于一组数据，并对一组数据返回一个值。</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840117.png" alt="image-20220610110622405"></p> <ul><li>聚合函数类型
<ul><li>AVG()</li> <li>SUM()</li> <li>MAX()</li> <li>MIN()</li> <li>COUNT()</li></ul></li> <li>聚合函数语法</li></ul> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840118.png" alt="image-20220610110707645"></p> <ul><li>聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。</li></ul> <h3 id="_1-1-avg和sum函数"><a href="#_1-1-avg和sum函数" class="header-anchor">#</a> 1.1 AVG和SUM函数</h3> <p>可以对<code>数值型数据</code>使用AVG 和 SUM 函数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token function">MIN</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> job_id <span class="token operator">LIKE</span> <span class="token string">'%REP%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840119.png" alt="image-20220610110810213"></p> <h3 id="_1-2-min和max函数"><a href="#_1-2-min和max函数" class="header-anchor">#</a> 1.2 MIN和MAX函数</h3> <p>可以对<code>任意数据类型</code>的数据使用 MIN 和 MAX 函数。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">MIN</span><span class="token punctuation">(</span>hire_date<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>hire_date<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employees<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840120.png" alt="image-20220610110845484"></p> <h3 id="_1-3-count函数"><a href="#_1-3-count函数" class="header-anchor">#</a> 1.3 COUNT函数</h3> <ul><li>COUNT(*)返回表中记录总数，适用于<code>任意数据类型</code>。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token number">50</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840121.png" alt="image-20220610110928594"></p> <ul><li>COUNT(expr) 返回<code>expr不为空</code>的记录总数。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>commission_pct<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>  employees
<span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token number">50</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840122.png" alt="image-20220610110951523"></p> <ul><li><strong>问题：用count(*)，count(1)，count(列名)谁好呢?</strong>
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用<code>count(*)</code>,<code>count(1)</code>直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好
于具体的count(列名)。</li> <li>*<strong>问题：能不能使用count(列名)替换count()?</strong>
不要使用 count(列名)来替代  <code>count(*)</code>， <code>count(*)</code>是 SQL92 定义的标准统计行数的语法，跟数
据库无关，跟 NULL 和非 NULL 无关。
说明：<code>count(*)</code>会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。</li></ul> <h2 id="_2-group-by"><a href="#_2-group-by" class="header-anchor">#</a> 2. GROUP BY</h2> <h3 id="_2-1-基本使用"><a href="#_2-1-基本使用" class="header-anchor">#</a> 2.1 基本使用</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840124.png" alt="image-20220610111228457"></p> <p>可以使用GROUP BY子句将表中的数据分成若干组</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">column</span><span class="token punctuation">,</span> group_function<span class="token punctuation">(</span><span class="token keyword">column</span><span class="token punctuation">)</span>
<span class="token keyword">FROM</span> <span class="token keyword">table</span>
<span class="token punctuation">[</span><span class="token keyword">WHERE</span> condition<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">GROUP</span> <span class="token keyword">BY</span> group_by_expression<span class="token punctuation">]</span>
<span class="token punctuation">[</span><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token keyword">column</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><blockquote><p><strong>明确：WHERE一定放在FROM后面</strong></p></blockquote> <p>在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  department_id<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>   employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840125.png" alt="image-20220610111332364"></p> <p>包含在 GROUP BY 子句中的列不必包含在SELECT 列表中</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>   employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840126.png" alt="image-20220610111413114"></p> <h3 id="_2-2-使用多个列分组"><a href="#_2-2-使用多个列分组" class="header-anchor">#</a> 2.2 使用多个列分组</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840127.png" alt="image-20220610111430526"></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  department_id dept_id<span class="token punctuation">,</span> job_id<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>   employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id<span class="token punctuation">,</span> job_id <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840128.png" alt="image-20220610111447535"></p> <h3 id="_2-3-group-by中使用with-rollup"><a href="#_2-3-group-by中使用with-rollup" class="header-anchor">#</a> 2.3 GROUP BY中使用WITH ROLLUP</h3> <p>使用 <code>WITH ROLLUP</code> 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所
有记录的总和，即统计记录数量。</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> department_id<span class="token punctuation">,</span><span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span> employees
<span class="token keyword">WHERE</span> department_id <span class="token operator">&gt;</span> <span class="token number">80</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id <span class="token keyword">WITH ROLLUP</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><blockquote><p>注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。</p></blockquote> <h2 id="_3-having"><a href="#_3-having" class="header-anchor">#</a> 3. HAVING</h2> <h3 id="_3-1-基本使用"><a href="#_3-1-基本使用" class="header-anchor">#</a> 3.1 基本使用</h3> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840129.png" alt="image-20220610111541396"></p> <p><strong>过滤分组：HAVING子句</strong></p> <blockquote><ol><li>行已经被分组。</li> <li>使用了聚合函数。</li> <li>满足HAVING 子句中条件的分组将被显示。</li> <li>HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。</li></ol></blockquote> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840130.png" alt="image-20220610111610155"></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  department_id<span class="token punctuation">,</span> <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>   employees
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id
<span class="token keyword">HAVING</span>  <span class="token function">MAX</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span><span class="token operator">&gt;</span><span class="token number">10000</span> <span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840131.png" alt="image-20220610111628574"></p> <ul><li>**非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。**如下：</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>  department_id<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span>
<span class="token keyword">FROM</span>   employees
<span class="token keyword">WHERE</span>  <span class="token function">AVG</span><span class="token punctuation">(</span>salary<span class="token punctuation">)</span> <span class="token operator">&gt;</span> <span class="token number">8000</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> department_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840132.png" alt="image-20220610111703524"></p> <h3 id="_3-2-where和having的对比"><a href="#_3-2-where和having的对比" class="header-anchor">#</a> 3.2 WHERE和HAVING的对比</h3> <ul><li>区别1：</li></ul> <p><strong>WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；</strong> <strong>HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。</strong>
这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。</p> <ul><li>区别2：</li></ul> <p><strong>如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。</strong> 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。
小结如下：</p> <p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840133.png" alt="image-20220610111847026"></p> <p><strong>开发中的选择：</strong>
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。</p> <h2 id="_4-select的执行过程"><a href="#_4-select的执行过程" class="header-anchor">#</a> 4. SELECT的执行过程</h2> <h3 id="_4-1-查询的结构"><a href="#_4-1-查询的结构" class="header-anchor">#</a> 4.1 查询的结构</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">#方式1：</span>
<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">WHERE</span> 多表的连接条件
<span class="token operator">AND</span> 不包含组函数的过滤条件
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">HAVING</span> 包含组函数的过滤条件
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ASC</span><span class="token operator">/</span><span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<span class="token comment">#方式2：</span>
<span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">JOIN</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">ON</span> 多表的连接条件
<span class="token keyword">JOIN</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">ON</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">WHERE</span> 不包含组函数的过滤条件
<span class="token operator">AND</span><span class="token operator">/</span><span class="token operator">OR</span> 不包含组函数的过滤条件
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">HAVING</span> 包含组函数的过滤条件
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ASC</span><span class="token operator">/</span><span class="token keyword">DESC</span>
<span class="token keyword">LIMIT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<span class="token comment">#其中：</span>
<span class="token comment">#（1）from：从哪些表中筛选</span>
<span class="token comment">#（2）on：关联多表查询时，去除笛卡尔积</span>
<span class="token comment">#（3）where：从表中筛选的条件</span>
<span class="token comment">#（4）group by：分组依据</span>
<span class="token comment">#（5）having：在统计结果中再次筛选</span>
<span class="token comment">#（6）order by：排序</span>
<span class="token comment">#（7）limit：分页</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br></div></div><h3 id="_4-2-select执行顺序"><a href="#_4-2-select执行顺序" class="header-anchor">#</a> 4.2 SELECT执行顺序</h3> <p>你需要记住 SELECT 查询时的两个顺序：</p> <p><strong>1.关键字的顺序是不能颠倒的：</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">FROM</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">HAVING</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">LIMIT</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">FROM</span> <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">WHERE</span> <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">HAVING</span> <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">SELECT</span> 的字段 <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">DISTINCT</span> <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token operator">-</span><span class="token operator">&gt;</span> <span class="token keyword">LIMIT</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="https://lcfimgsbed.oss-cn-chengdu.aliyuncs.com/img/202207250840134.png" alt="image-20220610112038424"></p> <p>比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> player_id<span class="token punctuation">,</span> player_name<span class="token punctuation">,</span> <span class="token function">count</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">as</span> num <span class="token comment"># 顺序 5</span>
<span class="token keyword">FROM</span> player <span class="token keyword">JOIN</span> team <span class="token keyword">ON</span> player<span class="token punctuation">.</span>team_id <span class="token operator">=</span> team<span class="token punctuation">.</span>team_id <span class="token comment"># 顺序 1</span>
<span class="token keyword">WHERE</span> height <span class="token operator">&gt;</span> <span class="token number">1.80</span> <span class="token comment"># 顺序 2</span>
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> player<span class="token punctuation">.</span>team_id <span class="token comment"># 顺序 3</span>
<span class="token keyword">HAVING</span> num <span class="token operator">&gt;</span> <span class="token number">2</span> <span class="token comment"># 顺序 4</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> num <span class="token keyword">DESC</span> <span class="token comment"># 顺序 6</span>
<span class="token keyword">LIMIT</span> <span class="token number">2</span> <span class="token comment"># 顺序 7</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p>在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 <code>虚拟表</code> ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。</p> <h3 id="_4-3-sql-的执行原理"><a href="#_4-3-sql-的执行原理" class="header-anchor">#</a> 4.3 SQL 的执行原理</h3> <p>SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：</p> <ol><li>首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；</li> <li>通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；</li> <li>添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。</li></ol> <p>当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。</p> <p>当我们拿到了查询数据表的原始数据，也就是最终的虚拟表  <code>vt1</code> ，就可以在此基础上再进行  <code>WHERE 阶段</code> 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表  <code>vt2</code> 。
然后进入第三步和第四步，也就是  <code>GROUP</code> 和 <code>HAVING 阶段</code> 。在这个阶段中，实际上是在虚拟表 vt2 的
基础上进行分组和分组过滤，得到中间的虚拟表  vt3 和  vt4 。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到  <code>SELECT</code> 和 <code>DISTINCT阶段</code> 。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 <code>vt5-1</code>和  <code>vt5-2</code> 。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是  <code>ORDER BY 阶段</code>，得到虚拟表  <code>vt6</code> 。
最后在 vt6 的基础上，取出指定行的记录，也就是  <code>LIMIT 阶段</code> ，得到最终的结果，对应的是虚拟表 <code>vt7</code> 。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，<strong>所谓底层运行的原理，就是我们刚才讲到的执行顺序。</strong></p></div></div> <!----> <div class="page-edit"><div class="edit-link"><a href="https://github.com/lcfqzd/vuepress-theme-vdoing/edit/master/docs/04.数据库/01.MySQL笔记/08.聚合函数.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <div class="tags"><a href="/noteslibrary/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/noteslibrary/pages/8de5b4/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">单行函数</div></a> <a href="/noteslibrary/pages/3ea5ac/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">子查询</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/noteslibrary/pages/8de5b4/" class="prev">单行函数</a></span> <span class="next"><a href="/noteslibrary/pages/3ea5ac/">子查询</a>→
      </span></p></div></div></div> <!----></main></div> <div class="footer"><!----> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2018-2023
    <span>LCFQZD | MIT License</span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"></div></div>
    <script src="/noteslibrary/assets/js/app.dc0c3d24.js" defer></script><script src="/noteslibrary/assets/js/2.a664539c.js" defer></script><script src="/noteslibrary/assets/js/95.f7232d68.js" defer></script>
  </body>
</html>
